﻿using HCQ2_Common;
using HCQ2_Common.Data;
using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model.VLine.InputDto;
using HCQ2_Model.VLine.OutputDto;
using HCQ2_Model.VLine.Params;
using HCQ2_Model.VR.Params;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VLionsRecordDAL: IT_VLionsRecordDAL
    {
        /// <summary>
        ///  VLions获取体验记录列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<RecordResult> GetRecordList(RecordModel param, out int total)
        {
            sb?.Clear();
            string str = $@"SELECT TOP {param.rows} * FROM (SELECT er_id,user_id,user_name,file_id,file_name,file_code,CONVERT(varchar(100),in_date,120) AS in_date,CONVERT(varchar(100), out_date, 120) AS out_date, em_time,ROW_NUMBER() OVER(ORDER BY in_date DESC)AS rowNum ";
            sb.Append(" FROM T_VLionsRecord WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.userName))
                sb.Append($" AND user_name='{param.userName}' ");
            if (!string.IsNullOrEmpty(param.fileName))
                sb.Append($" AND file_name LIKE '%{param.fileName}%' ");
            if (!string.IsNullOrEmpty(param.fileCode))
                sb.Append($"  AND file_code='{param.fileCode}' ");
            total = Helper.ToInt(SqlHelper.ExecuteScalar("SELECT COUNT(0)AS num " + sb.ToString()));
            DataTable dt = SqlHelper.ExecuteDataTable(str + sb.ToString() + $")record WHERE record.rowNum>{(param.page - 1) * param.rows}");
            return DataTableHelper.DataTableToIList<RecordResult>(dt);
        }
        /// <summary>
        ///  获取VLions作品排行
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<List<VLionsWorkRank>> GetProductionRank(VLionsRank model, List<int> per_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {model.size} *,CONVERT(DECIMAL(18,2),r.lens/60/60)AS em_time FROM (
            SELECT record.file_id,record.file_name,record.file_code,SUM(CONVERT(DECIMAL(18,2),record.em_time))AS lens FROM 
            (SELECT folder_id FROM T_DocFolderPermissRelation WHERE per_id IN({string.Join(",", per_id).Trim('\'')})
            UNION SELECT folder_id FROM dbo.T_DocumentFolder WHERE if_remove=0 AND if_sys=1 AND (create_id={model.user_id} OR if_limit=0)) per INNER JOIN
            (SELECT folder_id,file_id FROM dbo.T_DocumentFolderRelation) folder ON per.folder_id=folder.folder_id  INNER JOIN
            (SELECT * FROM dbo.T_VLionsRecord WHERE 1=1 ");
            //是否查询个人数据
            if (model.isPerson > 0 && model.user_id > 0)
                sb.Append($" AND user_id={model.user_id} ");
            string inDate = string.Empty, outDate = string.Empty;
            if (!string.IsNullOrEmpty(model.start_date))
                inDate = DateTime.ParseExact(model.start_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(model.end_date))
                outDate = DateTime.ParseExact(model.end_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            //开始时间、结束时间
            if (!string.IsNullOrEmpty(model.start_date) && !string.IsNullOrEmpty(model.end_date))
                sb.Append($" AND in_date>='{inDate}' AND in_date<='{inDate}' ");
            else if (!string.IsNullOrEmpty(model.start_date) && string.IsNullOrEmpty(model.end_date))
                sb.Append($" AND in_date>='{inDate}' ");
            else if (string.IsNullOrEmpty(model.start_date) && !string.IsNullOrEmpty(model.end_date))
                sb.Append($" AND in_date<='{outDate}' ");
            sb.Append(")record ON record.file_id = folder.file_id GROUP BY record.file_id,record.file_code,record.file_name)r ORDER BY r.lens DESC;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return await Task.Run(() => HCQ2_Common.Data.DataTableHelper.DataTableToIList<VLionsWorkRank>(dt));
        }
        /// <summary>
        ///  获取VLions用户排行
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<List<VLionsUserRank>> GetUserRank(VLionsRank model, List<int> per_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {model.size} users.*,CONVERT(DECIMAL(18,2),r.lens/60/60)AS em_time FROM (
            SELECT record.user_id,SUM(CONVERT(DECIMAL(18,2),record.em_time))AS lens FROM 
            (SELECT folder_id FROM T_DocFolderPermissRelation WHERE per_id IN({string.Join(",", per_id).Trim('\'')})
            UNION SELECT folder_id FROM dbo.T_DocumentFolder WHERE if_remove=0 AND if_sys=1 AND (create_id={model.user_id} OR if_limit=0)) per INNER JOIN
            (SELECT folder_id,file_id FROM dbo.T_DocumentFolderRelation) folder ON per.folder_id=folder.folder_id  INNER JOIN
            (SELECT user_id,file_id,em_time FROM dbo.T_VLionsRecord WHERE 1=1 ");
            string inDate = string.Empty, outDate = string.Empty;
            if (!string.IsNullOrEmpty(model.start_date))
                inDate = DateTime.ParseExact(model.start_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(model.end_date))
                outDate = DateTime.ParseExact(model.end_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            //开始时间、结束时间
            if (!string.IsNullOrEmpty(model.start_date) && !string.IsNullOrEmpty(model.end_date))
                sb.Append($" AND in_date>='{inDate}' AND in_date<='{inDate}' ");
            else if (!string.IsNullOrEmpty(model.start_date) && string.IsNullOrEmpty(model.end_date))
                sb.Append($" AND in_date>='{inDate}' ");
            else if (string.IsNullOrEmpty(model.start_date) && !string.IsNullOrEmpty(model.end_date))
                sb.Append($" AND in_date<='{outDate}' ");
            sb.Append(")record ON record.file_id = folder.file_id GROUP BY record.user_id)r INNER JOIN(SELECT user_id,user_name,login_name FROM dbo.T_User)users ON users.user_id = r.user_id ORDER BY r.lens DESC;");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return await Task.Run(() => HCQ2_Common.Data.DataTableHelper.DataTableToIList<VLionsUserRank>(dt));
        }

        //****************************************************5.0 VLines接口【1102需求】*******************************************************
        private string GetFolderType(int folder_type,string pageType="folder")
        {
            //目录类别
            string folderType = "VRManager";
            switch (folder_type)
            {
                case 1: folderType = "VRWorks"; break;
                case 2: folderType = "VRModule"; break;
                case 3: folderType = "DDCircle"; break;//度度圈
                case 4: folderType = "VRBuilding"; break;//楼宇
                case 5:
                case 6: folderType = "QDMeta"; break;//楼宇
                case 7: folderType = "QDMaterial"; break;//忆度元宇宙素材
            }
            if (pageType == "info")
            {
                if(folder_type==5)
                    folderType = "QDMetaProgram";
                else if (folder_type == 6)
                    folderType = "QDMetaRoom";
            }
            return folderType;
        }
        /// <summary>
        ///  1.1 获取用户收藏目录数
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<int> GetUserCollectFolderNum(UserCollectFolder model)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0)AS num FROM 
            (SELECT folder_id FROM dbo.T_VRUserFolder WHERE user_guid='{model.userid}')star INNER JOIN
            (SELECT folder_id FROM dbo.T_DocumentFolder WHERE page_type='{GetFolderType(model.folderType,"folder")}' AND pro_type={model.goodType} ");
            //分组名
            if (!string.IsNullOrEmpty(model.groupName))
                sb.Append($" AND group_name='{model.groupName}' ");
            sb.Append(")folder ON folder.folder_id = star.folder_id");
            return await Task.Run(()=> Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString())));
        }
        /// <summary>
        ///  1.2 获取用户参加的课堂数
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<int> GetUserStudyNum(UserClassRoom model)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0)AS num FROM 
            (SELECT DISTINCT RmId FROM dbo.N_VRUserAttend WHERE UserGuid='')attend INNER JOIN
            (SELECT RmId FROM dbo.N_VRLineClassRoom WHERE 1=1 ");
            string inDate = string.Empty, outDate = string.Empty;
            if (!string.IsNullOrEmpty(model.startDate))
                inDate = DateTime.ParseExact(model.startDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(model.endDate))
                outDate = DateTime.ParseExact(model.endDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            //开始时间、结束时间
            if (!string.IsNullOrEmpty(model.startDate) && !string.IsNullOrEmpty(model.endDate))
                sb.Append($" AND StartTime>='{inDate}' AND EndTime<='{outDate}' ");
            else if (!string.IsNullOrEmpty(model.startDate) && string.IsNullOrEmpty(model.endDate))
                sb.Append($" AND StartTime>='{inDate}' ");
            else if (string.IsNullOrEmpty(model.startDate) && !string.IsNullOrEmpty(model.endDate))
                sb.Append($" AND EndTime<='{outDate}' ");
            sb.Append(")room ON attend.RmId=room.RmId");
            return await Task.Run(() => Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString())));
        }
        /// <summary>
        ///  1.3 获取用户购买商品数
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<int> GetUserPayGoodNum(UserPayGoodNum model)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0)AS num FROM (SELECT OrderCode FROM dbo.N_Order WHERE OrderCreateUserId={model.user_id} AND (OrderState=2 OR OrderState=4) ");
            string inDate = string.Empty, outDate = string.Empty;
            if (!string.IsNullOrEmpty(model.startDate))
                inDate = DateTime.ParseExact(model.startDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(model.endDate))
                outDate = DateTime.ParseExact(model.endDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            //开始时间、结束时间
            if (!string.IsNullOrEmpty(model.startDate) && !string.IsNullOrEmpty(model.endDate))
                sb.Append($" AND PaymentTime>='{inDate}' AND PaymentTime<='{outDate}' ");
            else if (!string.IsNullOrEmpty(model.startDate) && string.IsNullOrEmpty(model.endDate))
                sb.Append($" AND PaymentTime>='{inDate}' ");
            else if (string.IsNullOrEmpty(model.startDate) && !string.IsNullOrEmpty(model.endDate))
                sb.Append($" AND PaymentTime<='{outDate}' ");
            sb.Append($@")o INNER JOIN (SELECT OrderCode FROM dbo.N_OrderCommodityInfo WHERE page_type='{GetFolderType(model.goodType,"info")}')c ON c.OrderCode = o.OrderCode;");
            return await Task.Run(() => Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString())));
        }
        /// <summary>
        ///  1.4 获取用户下载商品数
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<int> GetUserDownGoodNum(UserPayGoodNum model)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0)AS num FROM T_VRUserDownload WHERE user_guid='{model.userid}' AND page_type='{GetFolderType(model.goodType,"info")}' ");
            string inDate = string.Empty, outDate = string.Empty;
            if (!string.IsNullOrEmpty(model.startDate))
                inDate = DateTime.ParseExact(model.startDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(model.endDate))
                outDate = DateTime.ParseExact(model.endDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            //开始时间、结束时间
            if (!string.IsNullOrEmpty(model.startDate) && !string.IsNullOrEmpty(model.endDate))
                sb.Append($" AND down_date>='{inDate}' AND down_date<='{outDate}' ");
            else if (!string.IsNullOrEmpty(model.startDate) && string.IsNullOrEmpty(model.endDate))
                sb.Append($" AND down_date>='{inDate}' ");
            else if (string.IsNullOrEmpty(model.startDate) && !string.IsNullOrEmpty(model.endDate))
                sb.Append($" AND down_date<='{outDate}' ");
            return await Task.Run(() => Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString())));
        }
        /// <summary>
        ///  1.5 获取用户收藏的商品数
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<int> GetUserCollectGoodNum(UserPayGoodNum model)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0)AS num FROM 
            (SELECT file_id FROM T_VRFavoriteGoods WHERE user_id={model.user_id})star INNER JOIN
            (SELECT file_id FROM dbo.T_DocumentInfo WHERE page_type='{GetFolderType(model.goodType,"info")}')files ON star.file_id=files.file_id;");
            return await Task.Run(() => Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString())));
        }
        /// <summary>
        ///  1.6 获取用户点赞的商品数
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<int> GetUserLikeGoodNum(UserPayGoodNum model)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0)AS num FROM 
            (SELECT file_id FROM dbo.T_DocumentUserStar WHERE create_id={model.user_id})star INNER JOIN
            (SELECT file_id FROM dbo.T_DocumentInfo WHERE page_type='{GetFolderType(model.goodType,"info")}')files ON star.file_id=files.file_id;");
            return await Task.Run(() => Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString())));
        }
        /// <summary>
        ///  1.7 获取用户答题正确数
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<List<UserAnswerRatioResult>> GetUserAnswerNum(UserAnswerRatio model)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {model.size} aa.* FROM (SELECT accuracy=(CASE WHEN t.correct>0 THEN CAST((t.correct/t.total)*100 AS DECIMAL(18,2)) ELSE 0 END),
            * FROM (SELECT TOP {model.size} COUNT(0)AS total,CAST(SUM(status) AS DECIMAL(18,2))AS correct,file_id,file_code,file_name FROM (
            SELECT record.file_id,record.file_code,record.file_name,detial.status FROM 
            (SELECT er_id,file_id,file_code,file_name FROM dbo.T_VLionsRecord WHERE user_id={model.user_id} ");
            string inDate = string.Empty, outDate = string.Empty;
            if (!string.IsNullOrEmpty(model.startDate))
                inDate = DateTime.ParseExact(model.startDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(model.endDate))
                outDate = DateTime.ParseExact(model.endDate, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            //开始时间、结束时间
            if (!string.IsNullOrEmpty(model.startDate) && !string.IsNullOrEmpty(model.endDate))
                sb.Append($" AND in_date>='{inDate}' AND in_date<='{outDate}' ");
            else if (!string.IsNullOrEmpty(model.startDate) && string.IsNullOrEmpty(model.endDate))
                sb.Append($" AND in_date>='{inDate}' ");
            else if (string.IsNullOrEmpty(model.startDate) && !string.IsNullOrEmpty(model.endDate))
                sb.Append($" AND in_date<='{outDate}' ");
            sb.Append($@")record INNER JOIN (SELECT er_id,status FROM dbo.T_VLinesExperienceDetail)detial ON record.er_id=detial.er_id)c 
            GROUP BY c.file_id,c.file_code,c.file_name)t )aa ORDER BY aa.accuracy ");
            if (model.orderType > 0)
                sb.Append(" ASC; ");
            else
                sb.Append(" DESC; ");
            DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString());
            return await Task.Run(() => DataTableHelper.DataTableToIList<UserAnswerRatioResult>(dt));
        }
    }
}